<?php
/**
 * class App_Model_Blog
 *
 * @author kim, andrew
 */
class App_Model_Blog extends App_Model_Abstract
{
    // table name
    protected $_name = 'blogs';

    // blog status
    const STATUS_PENDING    = 'PENDING';
    const STATUS_APPROVED   = 'APPROVED';
    const STATUS_DELETED    = 'DELETED';
    const STATUS_DENIED     = 'DENIED';

    /**
     * get recent blogs in region
     */
    public function getRecentBlogsInRegion($localId = 0, $page = 0, $limit = 5)
    {
        $start = $page ? (($page - 1) * $limit) : 0;
        $query = "SELECT * FROM blog_questions WHERE status = '" . self::STATUS_APPROVED . "' ";

        if ($localId) {
            $locals = $this->fetchRow("SELECT * FROM locals WHERE id='$localId'");
            if (!empty($locals['parent_id_list'])) {
                $query .= " AND local_id_list LIKE '" . $locals['parent_id_list'] . "%'";
            }
        }
        $query .= " ORDER BY created_at DESC LIMIT $start, $limit";

        $blogs = $this->fetchAll($query);
        $this->expandBlogsPlace($blogs); // passed as reference
        $this->expandBlogsUser($blogs); // passed as reference

        $row = $this->fetchRow("SELECT FOUND_ROWS() AS count");
        $result['total_count'] = $row['count'];
        $result['blogs'] = $blogs;
        return $result;
    }

    /*************** COUNT functions ***************************************/

    /**
     * get total reviews count
     *
     * @param string $status
     * @throw exception
     * @return int
     */
    public function getCountByStatus($status)
    {
        if (self::STATUS_PENDING == $status OR
            self::STATUS_APPROVED == $status OR
            self::STATUS_DELETED == $status
        ) {
            $result = $this->fetchRow("SELECT COUNT(*) as num FROM blog_questions WHERE status = :status", array('status' => $status));
            return $result['num'];
        } else {
            throw new exception('Unknown status!');
        }
    }

    /*************** LIST functions *****************************************/

    /**
     * get recent blog_questions for a place
     */
    public function getRecentBlogsPlace($placeId, $page = 0, $limit = 5)
    {
        $start = $page ? (($page - 1) * $limit) : 0;
        $query = "SELECT * FROM blog_questions WHERE status = :status AND place_id = :place_id ORDER BY created_at DESC LIMIT $start, $limit";
        $blogs = $this->fetchAll($query, array('status' => self::STATUS_APPROVED, 'place_id' => $placeId), 60);
        $this->expandBlogsUser($blogs);
        return $blogs;
    }

    /**
     * get recent blog_questions for a user
     */
    public function getRecentBlogsUser($userId, $page = 0, $limit = 10)
    {
        $start = $page ? (($page - 1) * $limit) : 0;
        $query = "SELECT * FROM blog_questions WHERE status = :status AND user_id = :user_id ORDER BY created_at DESC LIMIT $start, $limit";
        $blogs = $this->fetchAll($query, array('status' => self::STATUS_APPROVED, 'user_id' => $userId), 60);
        $this->expandBlogsPlace($blogs);
        return $blogs;
    }

    /**
     * get blog count by user id (optional 'all' argument skips approval requirement)
     *
     * @param int $userId
     * @return array
     */
    public function getBlogsCountByUserId($userId, $all = 0) {
        if (!$all) {
            $approved = " AND status = '" . self::STATUS_APPROVED . "'";
        }
        $query = "SELECT COUNT(*) AS num FROM blog_questions WHERE user_id = '$userId' $approved";
        $result = $this->fetchRow($query);
        return $result['num'];
    }



    /**
     * return an array of top blogger ids
     * @params parameter array - e.g., user_id, local_id
     * @return questions array
     */
    public function getTopBloggers($data = array(), $page=1, $limit=30) {
        $start = $page ? (($page - 1) * $limit) : 0;
        $query = "SELECT user_id, COUNT(*) AS num_posts FROM blog_questions WHERE status='APPROVED' GROUP BY user_id ORDER BY num_posts DESC LIMIT $start, $limit";
        $qs = $this->fetchAll($query);
        //print "Q$query";
        //$query = "SELECT user_id, COUNT(*) AS num FROM blog_responses WHERE status='APPROVED' GROUP BY user_id ORDER BY num DESC";
        //$rs = $this->fetchAll($query);
        $userModel = new App_Model_User();
        foreach ($qs as &$q) {
          $q['user'] = $userModel->getUser($q['user_id']);
        }
        return $qs;
    }

    /**
     * increment blog view
     * @return null
     */
    public function recordQuestionView($questionId) {
      if ($myUser = $this->getStoredMyUser()) {
        $userId = $myUser[id];
      } else {
        $userId = -1; // means 'anonymous'
      }

      $ip = App_Model_Geo::getClientIp();
      $db = $this->getTable()->getAdapter();

      $test = $db->fetchRow("SELECT id, TO_DAYS(NOW())-TO_DAYS(created_day) AS days FROM blog_question_visits WHERE question_id=$questionId AND user_id=$userId AND ip='$ip' ORDER BY created_day DESC LIMIT 0,1");
      $days = (isset($test['id'])) ? $test['id'] : -1;

      // if never seen or seen more than 7 days ago, increment num_views counters and record a visit
      if ($days==-1 || $days>7) {
        $sql = "INSERT INTO blog_question_visits (user_id, question_id, ip, created_day) VALUES ($userId, $questionId, '$ip', NOW()) ON DUPLICATE KEY UPDATE id=id"; //ABC: yah, I don't remember how to ignore duplicate key if conflict
        $result = $db->query($sql);

        $db->query("UPDATE blog_questions SET num_views=num_views+1 WHERE id=$questionId");
      }
    }

    /**
     * return an array of questions according to params
     * @params parameter array - e.g., user_id, local_id
     * @return questions array
     */
    public function getQuestions($data = array(), $page=1, $limit=30) {
        $start = $page ? (($page - 1) * $limit) : 0;

        $recordVisits = isset($data['record_visits']) ? $data['record_visits'] : 0;
        $isReview = (isset($data['is_review']) && $data['is_review']==1) ? 1 : 0; // do extra for admin review

        $and='';
        if (isset($data['user_id'])) { $and.=" AND user_id=".$data['user_id']; }
        if (isset($data['status'])) {
          $and.=" AND bq.status='".$data['status']."' ";
        } else {
          $and.=" AND bq.status='APPROVED' ";
        }

        if (isset($data['local_id_list'])) { // find all the questions in a region
          $localIdList=$data['local_id_list'];
          $query = "SELECT SQL_CALC_FOUND_ROWS bq.* FROM blog_questions bq, users u WHERE bq.user_id=u.id AND local_id_list LIKE '$localIdList%' $and ORDER BY created_at DESC LIMIT $start,$limit";

        } elseif (isset($data['responses'])) { // find all the questions where user_id responded to
          if (isset($data['user_id'])) { $and=" AND br.user_id=".$data['user_id']; }
          $query = "SELECT SQL_CALC_FOUND_ROWS bq.* FROM blog_questions bq, blog_responses br WHERE bq.id=br.qid $and ORDER BY br.created_at DESC LIMIT $start,$limit";

        } elseif (isset($data['most_responses'])) { // find the questions with the most respondes
          $query = "SELECT SQL_CALC_FOUND_ROWS bq.*, COUNT(*) AS num_reviews FROM blog_questions bq, blog_responses br WHERE bq.id=br.qid AND bq.who_can_view=0 AND br.status='APPROVED' $and GROUP BY qid ORDER BY num_reviews DESC LIMIT $start,$limit";


        } elseif (isset($data['most_viewed'])) { // find the questions with the most respondes
          $query = "SELECT SQL_CALC_FOUND_ROWS bq.* FROM blog_questions bq WHERE bq.who_can_view=0 $and ORDER BY num_views DESC LIMIT $start,$limit";

        } else {
          $order = (isset($data['status']) && $data['status'] == 'pending') ? '' : 'DESC';
          $query = "SELECT SQL_CALC_FOUND_ROWS bq.* FROM blog_questions AS bq WHERE 1 $and ORDER BY created_at $order LIMIT $start,$limit";
        }

        //print "Q:$query<br>"; return;
        $results = $this->fetchAll($query);
            $row = $this->fetchRow("SELECT FOUND_ROWS() AS count");
            $return['total_count'] = $row['count'];

        $userModel = new App_Model_User();
        foreach ($results as &$question) {
          $userId=$question['user_id'];
          //$question['num_responses']=$this->countResponses($question['id'],array('user_id'=>$userId));
          $question['user']=$userModel->getUser($userId,array('reviews'=>1));
          //$question['user_num_questions']=$this->countUserQuestions($userId);
          //$question['user_num_responses']=$this->countUserResponses($userId);
          //$question['user_num_qa']=$question['user_num_questions'] + $question['user_num_responses'];

          if ($isReview) {
            $question['text_info'] = App_Filter_PreReviewText::filter($question['title'].$question['text']);
            $question['review'] = App_Filter_Textarea::filter($question['text']);
          }

          if ($recordVisits) {  $this->recordQuestionView($question['id']); }
        }

        $return['results'] = $results;

        return $return;
    }

    /** return a question given a question_id **/
    public function getQuestion($questionId) {
	    $question = $this->fetchRow("SELECT * FROM blog_questions WHERE id=:id",array('id'=>$questionId));
	    return $question;
    }

    /** return # of visable questions for a given userId **/
    public function countUserQuestions($userId) {
        $auth=" status='APPROVED' ";

        $myUser = App_Model_User::getStoredMyUser();
        if ($myUser && $userId == $myUser['id']) { // include pending and denied response by this user
          $auth=" status!='DELETED' ";
        }
        $and="AND $auth";
        $count = $this->fetchRow("SELECT COUNT(*) AS num FROM blog_questions WHERE user_id=:uid $and",array('uid'=>$userId));
        return isset($count['num']) ? $count['num'] : 0;
    }

    /******************* RESPONSE functions *********************************************/

    /**
     * return an array of responses according to params
     * @params parameter array - e.g., user_id, question_id
     * @return responses array
     */
    public function getResponses($data = array(), $page=1, $limit=30) {
        $start = $page ? (($page - 1) * $limit) : 0;

        $getQuestion = (isset($data['get_question']) && $data['get_question']==1) ? 1 : 0; // look up question
        $isReview = (isset($data['is_review']) && $data['is_review']==1) ? 1 : 0; // do extra for admin review
        $viewerId = App_Model_User::getStoredMyUserId();
        $and='';
        if (isset($data['question_id'])) { $and.=" AND qid=".$data['question_id']; }
        if (isset($data['user_id'])) {     $and.=" AND user_id=".$data['user_id']; }
        if (isset($data['status'])) {
          $and.=" AND status='".$data['status']."' ";
        } else {
          if($viewerId) {
              $and.=" AND ((user_id=".$viewerId." AND status!='DELETED') OR status='APPROVED') ";
          }
          else
            $and.=" AND status='APPROVED' ";
        }

        $order = (isset($data['status']) && $data['status'] == 'pending') ? '' : 'DESC';
        $query = "SELECT SQL_CALC_FOUND_ROWS bq.* FROM blog_responses AS bq WHERE 1 $and ORDER BY created_at $order LIMIT $start,$limit";

        //print "Q:$query<br>";
        $results = $this->fetchAll($query);
            $row = $this->fetchRow("SELECT FOUND_ROWS() AS count");
            $return['total_count'] = $row['count'];

        $userModel = new App_Model_User();


        foreach ($results as &$response) {
          $userId = $response['user_id'];
          $response['user']=$userModel->getUser($userId);
          $response['user_num_questions']=$this->countUserQuestions($userId);
          $response['user_num_responses']=$this->countUserResponses($userId);
          $response['user_num_qa']=$response['user_num_questions'] + $response['user_num_responses'];

          if ($getQuestion) {
            $response['question']=$this->getQuestion($response['qid']);
            $response['question_user']=$userModel->getUser($response['question']['user_id']);
          }

          if ($isReview) {
            $response['text_info'] = App_Filter_PreReviewText::filter($response['text']);
            $response['review'] = App_Filter_Textarea::filter($response['text']);
          }

          if ($response['quote_rid']) {
            $quotedResponse = $this->getResponse($response['quote_rid']);
            if (isset($quotedResponse['user_id'])) {
              $response['quoted'] = $quotedResponse;
              $response['quotedUser'] = $userModel->getUser($quotedResponse['user_id']);
            }
          }
          $parents = array();
          $this->getParentReponses($parents, $response['rid']);
          $parents = array_reverse($parents);

          $response['parents'] = $parents;
        }

        $return['results'] = $results;
        return $return;
    }

    /** return a question given a question_id **/
    public function getResponse($responseId) {
        $response = $this->fetchRow("SELECT * FROM blog_responses WHERE id=:id",array('id'=>$responseId));
        return $response;
    }

    public function getParentReponses(&$parents, $parentId, $level = 0) {


        $viewerId = App_Model_User::getStoredMyUserId();
        if($viewerId) {
              $and =" AND ((user_id=".$viewerId." AND status!='DELETED') OR status='APPROVED') ";
          }
          else
            $and =" AND status='APPROVED' ";
        $userModel = new App_Model_User();
        $data = array("parent_id" => $parentId);
        $parent = $this->fetchRow("SELECT * FROM blog_responses WHERE id =:parent_id ".$and,$data);
        if($parent) {
            $level++;
            $userId = $parent['user_id'];
            $parent['user'] = $userModel->getUser($userId);
            $parent['depth'] = $level;
            $parents[] = $parent;
            if($parent['rid'] > 0) {
                $this->getParentReponses($parents, $parent['rid'], $level);
            }

        }

    }
    /** return the number of responses given a question_id **/
    public function countResponses($questionId, $data=array()) {
        $auth = " status='APPROVED' ";
        if (isset($data['viewer_id'])) { // include pending and denied response by this user
            $viewerId=$data['viewer_id'];
            $auth.="  ($auth OR (user_id='$viewerId' AND status!='DELETED')) ";
        }
        $and = "AND $auth";

        $response = $this->fetchRow("SELECT COUNT(*) AS num FROM blog_responses WHERE qid=:qid $and",array('qid'=>$questionId));
        return (isset($response)) ? $response['num'] : 0;
    }

        /** return # of visable responses for a given userId **/
    public function countUserResponses($userId) {
        $auth = " status='APPROVED' ";

        $myUser = App_Model_User::getStoredMyUser();
        if ($myUser && $userId == $myUser['id']) { // include pending and denied response by this user
            $auth = " status!='DELETED' ";
        }
        $and = "AND $auth";
        $count = $this->fetchRow("SELECT COUNT(*) AS num FROM blog_responses WHERE user_id=:uid $and", array('uid'=>$userId));
        return isset($count['num']) ? $count['num'] : 0;
    }

    /************************** ADD question/response functions ************************************/

    /** insert a new question **/
    public function addQuestion($data) {
        $userId = $this->_g($data,'user_id'); // or use myid
        $question = array('user_id' => $userId,
            'status' => self::STATUS_APPROVED,
            'sender_email' => $this->_g($data,'sender_email'),
            'receiver_email' => $this->_g($data,'receiver_email'),
            'praised_name' => $this->_g($data,'praised_name'),
            'praised_email' => $this->_g($data,'praised_email'),
            'title' => $this->_g($data,'title'),
            'text' => $this->_g($data,'text'),
            'mood' => $this->_g($data,'mood'),
            'who_can_respond' => $this->_g($data,'who_can_respond'),
            'who_can_view' => $this->_g($data,'who_can_view'),
            'date' => date('Y-m-d'),
            'created_at' => date('Y-m-d H:i:s')
        );
        $sql = "INSERT INTO blog_questions (" . implode(',', array_keys($question)) . ") "
             . "VALUES (:" . implode(', :', array_keys($question)) . ")";
             //echo $sql;die;
        $db = $this->getTable()->getAdapter();
        $result = $db->query($sql, $question);

        if ($db->lastInsertId()) {
            // get the static view object
            $viewRenderer = Zend_Controller_Action_HelperBroker::getStaticHelper('viewRenderer');
            $view = $viewRenderer->view;
            // send registration email
            $from = 'www.biaoyangxin.com';
            $subject = '表扬信，致“' . $question['praised_name'] . '”';
            //$subject = 'Thank you';
            $body = $view->partial('index/tpl-biaoyang.phtml', array('blog' => $question));
            $mail = new App_Model_Mail();
            $isSent = $mail->send($question['sender_email'], $subject, $body, $from);
            $isSent = $mail->send($question['receiver_email'], $subject, $body, $from);
            $isSent = $mail->send($question['praised_email'], $subject, $body, $from);
        }

        return $db->lastInsertId();
    }

    /**
     * send add blog emails
     */
    public function addQuestionEmails($emails)
    {
    }

    /** delete a question **/
    public function deleteQuestion($id)
    {
    	$sql = "UPDATE blog_questions SET status = :status WHERE id = :id";
    	$db = $this->getTable()->getAdapter();
    	$question = array('id' => $id, 'status' => 'DELETED');
        $result = $db->query($sql, $question);
        return $result;
    }

    /** update a new question **/
    public function updateQuestion($data) {
        $question = array(
                  'id' => $this->_g($data, 'id'),
                  'title' => $this->_g($data,'title'),
                  'text' => $this->_g($data,'text'),
                  'mood' => $this->_g($data,'mood'),
                  'who_can_respond' => $this->_g($data,'who_can_respond'),
                  'who_can_view' => $this->_g($data,'who_can_view'),
                  'pre_review' => $this->_g($data,'pre_review')
                  );
        $sql = "UPDATE blog_questions SET title = :title, text = :text, mood = :mood, who_can_respond = :who_can_respond, who_can_view = :who_can_view, pre_review = :pre_review WHERE id = :id";
        $db = $this->getTable()->getAdapter();
        $result = $db->query($sql, $question);
        return $result;
    }

    public function updatePhotoId($id, $photoId) {
        $sql = "UPDATE blog_questions SET photo_id = :photo_id WHERE id = :id";
        $db = $this->getTable()->getAdapter();
        $result = $db->query($sql, array('id'=>$id, 'photo_id'=>$photoId));
        return $result;
    }

    /** insert a new response **/
    public function addResponse($data) {
        $qid = $this->_g($data,'qid');
	    $q = $this->fetchRow("SELECT * FROM blog_questions WHERE id='$qid'");
        if ($q['id']) {
            $response = array('qid' => $qid,
                'quote_rid' => $this->_g($data,'qrid'),
                'user_id' => $this->_g($data,'user_id'),
                'quser_id' => $q['user_id'],
                'rid' => $this->_g($data, 'prid'),
                'text' => $this->_g($data,'text'),
                'date' => date('Y-m-d'),
                'created_at' => date('Y-m-d H:i:s')
                );
            $sql = "INSERT INTO blog_responses (qid, quote_rid, user_id, quser_id, rid, text, date, created_at) "
                 . "VALUES (:qid, :quote_rid, :user_id, :quser_id, :rid, :text, :date, :created_at)";
            $db = $this->getTable()->getAdapter();
            $result = $db->query($sql, $response);
            return $db->lastInsertId();
        } else {
            return false;
        }
    }

    /************************** Admin functions ************************************/

    public function reviewBlogQuestions($params)
    {
        $actions = $params['actions'];
        $seconds = $params['seconds'];

        $db = $this->getTable()->getAdapter();
        $admin = App_Model_Admin_Administrator::getStoredMyAdmin();
        $adminId = $admin['id'];

        $countApprove = 0;
        $countDeny = 0;
        foreach ($actions as $id => $data) {
            if ($data['action'] == 'approve') {
                $status = App_Model_Review::STATUS_APPROVED;
                $countApprove++;
            } else if ($data['action'] == 'deny') {
                $status = App_Model_Review::STATUS_DENIED;
                $countDeny++;
            }
            $sql = "UPDATE blog_questions SET status = :status, title = :title, text = :text WHERE id = :id";
            $db->query($sql, array('status'=>$status,
                                   'title'=>$data['title'],
                                   'text'=>$data['text'],
                                   'id'=>$id));
        }

        // save approve/deny counts in Aministrator::logAdminWork function
        $admin = new App_Model_Admin_Administrator();
        $adminLog = array('type'=>'blog_question_review', 'seconds'=>$seconds, 'counts'=>array('approve'=>$countApprove, 'deny'=>$countDeny));
        $admin->logAdminWork($adminLog);
        return true;
    }

    public function reviewBlogResponses($params)
    {
        $actions = $params['actions'];
        $seconds = $params['seconds'];

        $db = $this->getTable()->getAdapter();
        $admin = App_Model_Admin_Administrator::getStoredMyAdmin();
        $adminId = $admin['id'];

        $countApprove = 0;
        $countDeny = 0;
        foreach ($actions as $id => $data) {
            if ($data['action'] == 'approve') {
                $status = App_Model_Review::STATUS_APPROVED;
                $countApprove++;
            } else if ($data['action'] == 'deny') {
                $status = App_Model_Review::STATUS_DENIED;
                $countDeny++;
            }
            $sql = "UPDATE blog_responses SET status = :status, text = :text WHERE id = :id";
            $db->query($sql, array('status'=>$status,
                                   'text'=>$data['text'],
                                   'id'=>$id));
        }

        // save approve/deny counts in Aministrator::logAdminWork function
        $admin = new App_Model_Admin_Administrator();
        $adminLog = array('type'=>'blog_response_review', 'seconds'=>$seconds, 'counts'=>array('approve'=>$countApprove, 'deny'=>$countDeny));
        $admin->logAdminWork($adminLog);
        return true;
    }


}
